/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.com.pps.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import vn.com.pps.entities.Teacher;

/**
 *
 * @author Dai
 */
public class TeacherDAO extends BaseDAO implements ITeacherDAO {

    @Override
    public int insert(Teacher teacher) {
        String sql = "INSERT INTO TblTeachers(ID,Firstname,Lastname,Mobile,Email,Note,CreatedOn,LastModified) VALUES(?,?,?,?,?,?,?,?)";
        Object[] params = new Object[]{null, teacher.getFirstName(), teacher.getLastName(),
            teacher.getMobile(), teacher.getEmail(),
            teacher.getNote(), teacher.getCreatedDate(), teacher.getLastModifiedDate()
        };
        PreparedStatementCreatorFactory psc
                = new PreparedStatementCreatorFactory(sql);
        psc.addParameter(new SqlParameter("ID", Types.INTEGER));
        psc.addParameter(new SqlParameter("Firstname", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Lastname", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Mobile", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Email", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Note", Types.VARCHAR));
        psc.addParameter(new SqlParameter("CreatedOn", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
        psc.setReturnGeneratedKeys(true);
        KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(psc.newPreparedStatementCreator(params),
                generatedKeyHolder);
        return generatedKeyHolder.getKey().intValue();
    }

    @Override
    public int update(Teacher teacher) {
        System.out.println("@update DAO");
        System.out.println("Test:"+teacher.getNote());
        //String sql="UPDATE TblTeachers SET Firstname='"+teacher.getFirstName()+"', Lastname='"+teacher.getLastName()+"',Mobile='"+teacher.getMobile()+"',Email='"+teacher.getEmail()+"',Note='"+teacher.getNote()+"',LastModified=CURRENT_TIMESTAMP WHERE ID="+teacher.getId();
        String sql = "UPDATE TblTeachers SET Firstname=?, Lastname=?,Mobile=?,Email=?,Note=?,LastModified=? WHERE ID=?";
        
        Object[] params = new Object[]{teacher.getFirstName(),teacher.getLastName(),
            teacher.getMobile(),teacher.getEmail(),
            teacher.getNote(),teacher.getLastModifiedDate(),teacher.getId()
        };
        PreparedStatementCreatorFactory psc =
                    new PreparedStatementCreatorFactory(sql);
            psc.addParameter(new SqlParameter("Firstname", Types.VARCHAR));
            psc.addParameter(new SqlParameter("Lastname", Types.VARCHAR));
            psc.addParameter(new SqlParameter("Mobile", Types.VARCHAR));
            psc.addParameter(new SqlParameter("Email", Types.VARCHAR));
            psc.addParameter(new SqlParameter("Note", Types.VARCHAR));
            psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
            psc.addParameter(new SqlParameter("ID", Types.INTEGER));
           getJdbcTemplate().update(psc.newPreparedStatementCreator(params));
           // getJdbcTemplate().update(sql);
            return teacher.getId();
    }

    @Override
    public int delete(int id) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public Page<Teacher> getTeachers(int pageNo, int pageSize, String whereClause) {
         String sqlQuery = "SELECT * FROM TblTeachers WHERE " + whereClause;
         System.out.println("Query::"+sqlQuery);
        String sqlQueryCount = "SELECT COUNT(*) FROM TblTeachers WHERE " + whereClause;
        System.out.println("COunt:"+sqlQueryCount);
        PaginationHelper<Teacher> ph = new PaginationHelper <Teacher>();
        return ph.fetchPage(this.getJdbcTemplate(), 
                sqlQueryCount,
                sqlQuery, 
                null, pageNo, 
                pageSize, 
                new ParameterizedRowMapper<Teacher>() {

             @Override
             public Teacher mapRow(ResultSet rs, int i) throws SQLException {
                 Teacher teacher = new Teacher();
                 teacher.setId(rs.getInt("ID"));
                 teacher.setFirstName(rs.getString("FirstName"));
                 teacher.setLastName(rs.getString("LastName"));
                 teacher.setMobile(rs.getString("Mobile"));
                 teacher.setEmail(rs.getString("Email"));
                 teacher.setNote(rs.getString("Note"));
                 teacher.setCreatedDate(rs.getTimestamp("CreatedOn"));
                 teacher.setLastModifiedDate(rs.getTimestamp("LastModified"));
                 return teacher;
             }
         });
    }

    @Override
    public int getCount(String whereClause) {
         String sqlQuery = "SELECT COUNT(*) FROM TblTeachers";
        return getJdbcTemplate().queryForInt(sqlQuery);
    }

    @Override
    public Teacher getPupilEvent(Integer id) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
    
    
    @Override
    public Teacher getTeacher(Integer id) {
        String sqlQuery = "SELECT * FROM TblTeachers WHERE ID="+id;
        return (Teacher)getJdbcTemplate().queryForObject(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("ID"));
                teacher.setFirstName(rs.getString("Firstname"));
                teacher.setLastName(rs.getString("Lastname"));
                teacher.setMobile(rs.getString("Mobile"));
                teacher.setEmail(rs.getString("Email"));
                teacher.setNote(rs.getString("Note"));
                teacher.setCreatedDate(rs.getTimestamp("CreatedOn"));
                teacher.setLastModifiedDate(rs.getTimestamp("LastModified"));
                return teacher;
            }
        });
    }

    @Override
    public List<Teacher> getTeachers() {
         String sqlQuery = "SELECT * FROM TblTeachers WHERE ID=";
        return getJdbcTemplate().query(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("ID"));
                teacher.setFirstName(rs.getString("Firstname"));
                teacher.setLastName(rs.getString("Lastname"));
                teacher.setMobile(rs.getString("Mobile"));
                teacher.setEmail(rs.getString("Email"));
                teacher.setNote(rs.getString("Note"));
                teacher.setCreatedDate(rs.getTimestamp("CreatedOn"));
                teacher.setLastModifiedDate(rs.getTimestamp("LastModified"));
                return teacher;
            }
        });
    }


}
